﻿<%@ Page Language="C#" MasterPageFile="~/_layouts/application.master" EnableViewState="true" EnableViewStateMac="false" %> 
<%@ Register Tagprefix="SharePoint" Namespace="Microsoft.SharePoint.WebControls" Assembly="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %> 
<%@ Register Tagprefix="Utilities" Namespace="Microsoft.SharePoint.Utilities" Assembly="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %> 
<%@ Register Tagprefix="wssawc" Namespace="Microsoft.SharePoint.WebControls" Assembly="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %> 
<%@ Register Tagprefix="SharePoint" Namespace="Microsoft.SharePoint.WebControls" Assembly="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Register TagPrefix="wssuc" TagName="LinkSection" src="~/_controltemplates/LinkSection.ascx" %>
<%@ Register Tagprefix="Meetings" Namespace="Microsoft.SharePoint.Meetings" Assembly="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Register Tagprefix="Workflow" Namespace="Microsoft.SharePoint.Workflow" Assembly="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<%@ Import Namespace="Microsoft.SharePoint.ApplicationPages" %> 
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Collections" %>
<%@ Import Namespace="Microsoft.SharePoint" %>
<%@ Import Namespace="System.Collections.Generic" %>
<%@ Import Namespace="System.Configuration" %>



<script type="text/C#" runat="server" >
    System.Collections.Generic.List<DictionaryEntry> spColumns;
    String RootSiteurl;
    static global::System.Data.OleDb.OleDbConnection xlConn;
    static String spListGuid;
    static String excelPath, excelExten;
    static System.Data.DataTable dt;
    string spColumnName, xlColumnName = string.Empty;
    
    protected void Page_Load(object sender, EventArgs e)
    {
            if (Request.QueryString["list"] != null)
                spListGuid = Request.QueryString["list"]; 
            else
               litMessage.Text= "Enter Correct List GUID";

            lblSuccessMessage.Text = string.Empty;      
    }

    protected void btnUpload_Click(object sender, EventArgs e)
    {
        try
        {
            CreateDirectory();
    
            excelPath =  Server.MapPath("/_layouts/Temp/" + fuExcelSheet.FileName );
            
            if (fuExcelSheet.FileName != string.Empty)
            {
                if (excelPath.EndsWith("xls", StringComparison.InvariantCultureIgnoreCase))
                {
                    excelExten = "xls";
                    xlConn = new global::System.Data.OleDb.OleDbConnection(CreateXlConnectionString(excelPath, excelExten));
                    GetExcelSheetNames();
                    btnShowData.Visible = true;
                    ddlSheetName.Visible = true;
                   
                    ClientScript.RegisterStartupScript(GetType(), "key", "document.getElementById('light').style.display='block';document.getElementById('fade').style.display='block';", true);
                }
                else if (excelPath.EndsWith("xlsx", StringComparison.InvariantCultureIgnoreCase))
                {
                    excelExten = "xlsx";
                    xlConn = new global::System.Data.OleDb.OleDbConnection(CreateXlConnectionString(excelPath, excelExten));
                    GetExcelSheetNames();
                    ClientScript.RegisterStartupScript(GetType(), "key", "document.getElementById('light').style.display='block';document.getElementById('fade').style.display='block';", true);
                    litMessage.Text = string.Empty;
                    btnShowData.Visible = true;
                    ddlSheetName.Visible = true;
                }
                else 
                {
                    litMessage.Text="Please Upload .xls or .xlsx file";
                }
            }
           else
            {
                litMessage.Text = "Please provide a valid file path";
            }
               
        }
            
        catch (Exception ex)
        {
            litMessage.Text = "Incorrect Format";
            rptBindColumns.Visible = false;
        }        
    }

    protected void rptBindColumns_OnItemDataBound(object sender, RepeaterItemEventArgs e)
    {
        try
        {
            if (!(e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)) return;

            DropDownList drpsplist = (DropDownList)e.Item.FindControl("drpSPlistColumns");
            drpsplist.DataSource = getXlColnames();
            drpsplist.DataTextField = "Value";
            drpsplist.DataValueField = "Key";
            drpsplist.DataBind();
        }
        catch(Exception ex)
        {
            throw ex;
        }
    }

    private String CreateXlConnectionString(String filePath, String ext)
    {
        String connString = string.Empty;
        if (ext == "xls")
            connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + filePath + ";Extended Properties=\"Excel 8.0;HDR=YES;\"";
        else if (ext == "xlsx")
            connString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + filePath + ";Extended Properties=\"Excel 12.0;HDR=YES;\"";
        return connString;
    }

    private System.Collections.Generic.List<DictionaryEntry> getXlColnames()
    {
        System.Collections.Generic.List<DictionaryEntry> XlColumns;
        System.Data.DataSet ds = new System.Data.DataSet();
        ds = getXldata();
    
        try
        {
            XlColumns = new System.Collections.Generic.List<DictionaryEntry>();
            foreach (System.Data.DataColumn xlClnm in ds.Tables[0].Columns)
            {
                XlColumns.Add(new DictionaryEntry(xlClnm.Caption, xlClnm.Caption));
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            // Conn.Close();
        }
        return XlColumns;

    }

    private System.Collections.Generic.List<DictionaryEntry> getSPListColumns()
    {
        SPList List = getSPList(spListGuid);
        spColumns = new System.Collections.Generic.List<DictionaryEntry>();
       
        foreach (SPField field in List.Fields)
        {
            if (field.Hidden == false && field.ReadOnlyField == false)
            {
                spColumns.Add(new DictionaryEntry(field.Title, field.Title));
            }
        }
          return spColumns;
    }

    private System.Data.DataSet getXldata()
    {
        xlConn = new global::System.Data.OleDb.OleDbConnection(CreateXlConnectionString(excelPath, excelExten));
        string sheetName = ddlSheetName.SelectedValue;
       
        string cmd = string.Format("select * from [{0}]", sheetName);
       
        System.Data.OleDb.OleDbCommand Comm = new System.Data.OleDb.OleDbCommand(cmd, xlConn);
        Comm.Connection = xlConn;
       
        System.Data.DataSet ds = new System.Data.DataSet();
        System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter();
        try
        {
            xlConn.Open();
           
            Comm.CommandType = System.Data.CommandType.Text;
            adapter.SelectCommand = Comm;
            adapter.Fill(ds);
           
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            xlConn.Close();
        }
        return ds;

    }
    
    private void GetExcelSheetNames()
    {
        xlConn.Open();
      
        dt = xlConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
        String[] excelSheets = new String[dt.Rows.Count];
       
        int i = 0;

        // Add the sheet name to the string array.
        foreach (System.Data.DataRow row in dt.Rows)
        {
            excelSheets[i] = row["TABLE_NAME"].ToString();
            i++;
        }
        ddlSheetName.DataSource = excelSheets;
        ddlSheetName.DataBind();
        xlConn.Close();
    }

    private SPList getSPList(String SPListGuid)
    {
        SPSite Site = SPContext.Current.Site;
        SPWeb web = Site.OpenWeb();
        Guid listid = new Guid(SPListGuid);
        web.AllowUnsafeUpdates = true;
        SPList List = web.Lists[listid];
        return List;
    }

    private void insertXlDataToSPList(String XlSPLists)
    {
        System.Data.DataSet dsXldata = new System.Data.DataSet();
        dsXldata = getXldata();
      
        String[] XlSpArray = XlSPLists.Split(';');
        SPList List = getSPList(spListGuid);
        int xlrow = 0;
        bool isRowEmpty;
        foreach (System.Data.DataRow dr in dsXldata.Tables[0].Rows)
        {
            xlrow += 1;
            isRowEmpty = true;
            try
            {
                SPListItem listitems = List.Items.Add();

                foreach (string myString in XlSpArray)
                {
                    string[] values = myString.Split('-');


                    if (listitems != null)
                    {
                        spColumnName = values[0];
                        xlColumnName = values[1];

                        if (dr[values[1]].ToString().Trim() != String.Empty)
                        {
                            isRowEmpty = false;
                            if (listitems.Fields[values[0]].GetType().Name == "SPFieldDateTime")
                            {
                                DateTime tempDateTime;
                                if (DateTime.TryParse(dr[values[1]].ToString(), out tempDateTime))
                                    listitems[values[0]] = tempDateTime.ToShortDateString();
                            }
                            else
                                listitems[values[0]] = dr[values[1]];  //values[1] --> Excelcolumn and values[0]---> SPfield name
                        }
                    }

                }
                if (!isRowEmpty) listitems.Update();
            }
            catch (Exception ex)
            {

                litValidationMessage.Text = " No. of Rows copied to SpList: " + (xlrow - 1) + "<br>" +
                                            " Type mismatch between the columns:" + " " + spColumnName + " - " + xlColumnName + " at row " + (xlrow + 1);
                ClientScript.RegisterStartupScript(GetType(), "key", "document.getElementById('light').style.display='block';document.getElementById('fade').style.display='block';", true);
                throw ex;


            }
        }
    }
    
   protected void btnImport_Click(object sender, EventArgs e)
    {
        try
        {
            string message = string.Empty;
            String XlSPLists = string.Empty;
            litValidationMessage.Text = string.Empty;
            litValidationErrorMessage.Text = string.Empty;
            foreach (RepeaterItem item in this.rptBindColumns.Items)
            {
                DropDownList ddlSPlistColumn = item.FindControl("drpSPlistColumns") as DropDownList;
                CheckBox chkXlColumn = item.FindControl("chkXlNames") as CheckBox;

                if (chkXlColumn.Checked == true)
                {

                    if (message.IndexOf(ddlSPlistColumn.SelectedValue) < 0)
                    {

                        message = message + ";" + ddlSPlistColumn.SelectedValue;
                        XlSPLists = XlSPLists + ";" + chkXlColumn.Text + "-" + ddlSPlistColumn.SelectedValue;
                    }
                    else
                    {
                        litValidationMessage.Text = "Repeated spreadsheet columns:" + " " + ddlSPlistColumn.SelectedValue;
                        ClientScript.RegisterStartupScript(GetType(), "key", "document.getElementById('light').style.display='block';document.getElementById('fade').style.display='block';", true);
                        break;
                    }
                }
            }
            if (litValidationMessage.Text == string.Empty && XlSPLists != String.Empty)
            {
                insertXlDataToSPList(XlSPLists.Substring(1));
                DeleteAllFiles();
                lblSuccessMessage.Text = "Added Successfully";
                rptBindColumns.Visible = false;
                ddlSheetName.Visible = false;
                btnget.Visible = false;
                litValidationMessage.Text = string.Empty;
                litValidationErrorMessage.Text = string.Empty;
                SPList List = getSPList(spListGuid);
                Response.Redirect(SPContext.Current.Site.Url + "/Lists/" + List.Title + "/AllItems.aspx");

            }
            else
            {
                litValidationMessage.Text = (litValidationMessage.Text != string.Empty) ? litValidationMessage.Text : "Select atleast one column";
                ClientScript.RegisterStartupScript(GetType(), "key", "document.getElementById('light').style.display='block';document.getElementById('fade').style.display='block';", true);
            }
        }
        catch (Exception ex)
        {
            
        }
    }

   protected void btnShowData_Click(object sender, EventArgs e)
    {
        try
        {
            xlConn = new global::System.Data.OleDb.OleDbConnection(CreateXlConnectionString(excelPath, excelExten));
            rptBindColumns.DataSource = getSPListColumns(); //getXlColnames(xlConn);
            rptBindColumns.DataBind();
            ClientScript.RegisterStartupScript(GetType(), "key", "document.getElementById('light').style.display='block';document.getElementById('fade').style.display='block';", true);
            btnget.Visible = true;
            rptBindColumns.Visible = true;
            litValidationMessage.Text = "";
            litValidationMessage.Text = "";
        }
        catch
        {
            lblSuccessMessage.Text = "Selected worksheet is not correctly formatted. Please try again.";
        }
    }

    private void CreateDirectory()
    {
        if (fuExcelSheet.HasFile)
            try
            {
              String path = Server.MapPath("/_layouts/Temp/");
              if (System.IO.Directory.Exists(path))
                 UploadFiles(path);
               else
                {
                   System.IO.Directory.CreateDirectory(path);
                    UploadFiles(path);
                }
            }
            catch (Exception ex)
            {
                litMessage.Text = "ERROR: " + ex.Message.ToString();
            }
       
    }
    private void DeleteAllFiles()
    {
        string path = Server.MapPath("/_layouts/Temp/");

        if (System.IO.Directory.Exists(path))
        {
            System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(path);
            System.IO.FileInfo[] files=dir.GetFiles();
            if (files.Length > 0)
            {
                for (int i = 0; i < files.Length - 1; i++)
                {
                    files[i].Delete();
                }
            }
        }
    }
    private void UploadFiles(String Path)
    {
        fuExcelSheet.SaveAs(Path + "/" + fuExcelSheet.FileName);
       

    }
</script>
<asp:Content contentplaceholderid="PlaceHolderPageTitle" runat="server">
   Import from spreadsheet
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderId="PlaceHolderPageDescriptionRowAttr" runat="server">
</asp:Content>
<asp:Content ID="Content3" ContentPlaceHolderId="PlaceHolderPageDescriptionRowAttr2" runat="server">
</asp:Content>
<asp:Content ID="Content4" ContentPlaceHolderId="PlaceHolderAdditionalPageHead" runat="server">
</asp:Content>
<asp:Content ID="Content5" ContentPlaceHolderId="PlaceHolderPageTitleInTitleArea" runat="server">
</asp:Content>
<asp:Content ID="Content6" ContentPlaceHolderId="PlaceHolderPageImage" runat="server">

</asp:Content>
<asp:Content ID="Content7" ContentPlaceHolderId="PlaceHolderPageDescription" runat="server">
</asp:Content>
<asp:Content ContentPlaceHolderID="PlaceHolderMain" runat="server">
<script type="text/javascript">
     function hide()
     {
        
        document.getElementById('light').style.display='none';
       
     }
</script>
<style type="text/css">
    #fade
    {
	    background-color:#525252;
    	
	    filter: alpha(opacity=85);
	    height:100%;
	    left:0px;
	    opacity:.85;
	    position:absolute;
	    top:00px;
	    right:0px;
	    display:none;
	    width:100%;
	    z-index:1000;
        
               
            }
  
    #light {
       display:none;
        position: relative;
              width: 75%;
      margin:auto;
       font-family:Tahoma;
       font-size:12px
    }
    .uploadBackGround
    {
        background-color:#EBF3FF;
        height:100px;
        text-align:center;
        font-family:tahoma;
        font-size:12px;
        color:#525252;
    }
    .leftDescription
    {
        font-family:tahoma;
        font-size:12px;
        color:#525252;
        line-height:15px;
        font-weight:bold;
      

    }
    .btn,.btnLarge,.btnUploadExcel
    {
        border:1px solid #454545;
        color:#000000;
        font-family:tahoma;
        font-size:11px;
        font-style:normal;
        font-variant:normal;
        font-weight:normal;
        line-height:normal;
        padding:2px;
        
    }
   .btnLarge,.btnUploadExcel
   {
       width:70px;
       height:30px;
   }
    .ErrorMessage
    {
        color:red;
        font-weight:bold;
        font-family:tahoma;
        font-size:11px;
        text-align:center;
        padding:5px;
    }
    .chk,.listHeader
    {
        
        font-family:tahoma;
        font-size:12px;
        font-style:normal;
        font-variant:normal;
        line-height:normal;
    }
    .divItems
    {
        padding:25px;
    }
        
    .listHeader
    {
        
        font-size:14px;
        color:#003399;
        
    }
        
   html,body{height:100%;}
     .b1f, .b2f, .b3f, .b4f{font-size:1px; overflow:hidden; display:block;}
    .b1f {height:1px; background:#EBF3FF; margin:0 5px;}
    .b2f {height:1px; background:#EBF3FF; margin:0 3px;}
    .b3f {height:1px; background:#EBF3FF; margin:0 2px;}
    .b4f {height:2px; background:#EBF3FF; margin:0 1px;}
    
    .contentf {background: #EBF3FF;width:100%}
    .contentf div{margin-left: 5px;}
    .itemCss{float:left;width:400px;padding:5px;color:#ffffff}
</style>
 <div style="padding:25px;">
    <table width="100%" border="0" cellpadding="0" cellspacing="0">
        <tr>
            <td style="width:70%;border-bottom:1px solid #4F72A7;border-top:1px solid #4F72A7" class="leftDescription">
                Upload Excel Sheet<br /><br />
                <div style="font:11px;font-weight:normal">Browse to excel file which you intend to upload </div>
            </td>
            <td style="width:30%;border-bottom:1px solid #4F72A7;border-top:1px solid #4F72A7;border-left:1px solid #4F72A7" valign="middle" class="uploadBackGround">
               
                    <asp:FileUpload ID="fuExcelSheet" runat="server" CssClass="btn"/>
                   
                    <div><asp:label runat="server" ID="litMessage"  CssClass="ErrorMessage"></asp:label></div>

            </td>
        </tr>
        <tr>
            <td></td>
            <td align="center"  style="padding:15px"><asp:Button ID="btnUpload" runat="server" OnClick="btnUpload_Click" Text="Upload" CssClass="btn" /> </td>
        </tr>
    </table>
       
	<div id="light">
    <b class="b1f"></b><b class="b2f"></b><b class="b3f"></b><b class="b4f"></b> 
    <div style="background-color:#EBF3FF;padding:25px">
        <div style="color:#4F72A7;font-weight:bold;float:left;padding:5px">Select your worksheet:</div>
         <div>
            <asp:DropDownList ID="ddlSheetName" runat="server" />&nbsp;&nbsp;<asp:Button ID="btnShowData" runat="server" Visible="false" OnClick="btnShowData_Click" Text="DisplayColumns" CssClass="btn"/>
         </div>
            <asp:Label ID="litValidationErrorMessage" runat="server"></asp:Label>
              <div class="divItems">
                  <asp:Repeater ID="rptBindColumns" runat="server" OnItemDataBound="rptBindColumns_OnItemDataBound">
                  <HeaderTemplate>
                        <table width="100%" cellpadding="10px" cellspacing="0">
                            <tr><td colspan="2" align="left" style="font-weight:bold;color:#4F72A7;padding:5px;font-size:12px">Map the columns</td></tr>
                            <tr>
                                <td style="width:50%;font-weight:bold;background-color:#4F72A7;color:White;border-right:1px solid white;text-align:center;font-size:12px;">SharePoint List Columns</td>
                                <td style="width:50%;font-weight:bold;background-color:#4F72A7;color:White;border-left:1px solid white;text-align:center;font-size:12px;">WorkSheet Columns</td>
                            </tr>
                        </table>
                  </HeaderTemplate>
                    <ItemTemplate>
                        <table width="100%"  cellpadding="10px" cellspacing="0">
                            <tr>
                                <td style="width:50%;border:1px solid #ffffff;padding-left:10px"><asp:CheckBox ID="chkXlNames" Text='<%#Eval("Value")%>' CssClass="chk" runat="server"/></td>
                                <td style="width:50%;border:1px solid #ffffff;padding-left:10px"><asp:DropDownList ID="drpSPlistColumns" runat="server"/></td>
                            </tr>
                        </table>
                    </ItemTemplate>
                </asp:Repeater>
                <div style="padding:25px"><asp:Button ID="btnget" Text="Import" Visible="false" runat="Server" OnClick="btnImport_Click" CssClass="btn"/></div>
                 <asp:Label ID="litValidationMessage" runat="server" CssClass="ErrorMessage"></asp:Label>
                </div> 
      </div>
      <b class="b4f"></b><b class="b3f"></b><b class="b2f"></b><b class="b1f"></b>
      </div>
      <div style="text-align:center;width:100%;top:25%;position:relative">
            <asp:Label ID="lblSuccessMessage" runat="server"  CssClass="ErrorMessage" ></asp:Label>
      </div>
 </div>
</asp:Content>